from mysql import connector

from core.util import CodeUtil

staticConnectors = dict()

"""获取连接"""


def get_connector(user: str = 'root', password: str = 'root', host: str = '127.0.0.1', database: str = 'test'):
    cnxCode = CodeUtil.md5(host + database)
    if staticConnectors.get(cnxCode) is not None:
        cnx = staticConnectors.get(cnxCode)
        # 连接一旦关闭， 重开连接
        if cnx.is_connected() is False:
            staticConnectors.pop(cnxCode)
            return get_connector(user=user, password=password, host=host, database=database)
        else:
            return cnx
    else:
        cnx = ___connect(user=user, password=password, host=host, database=database)
        staticConnectors[cnxCode] = cnx
        return cnx


"""数据库连接"""


def ___connect(user: str = 'root', password: str = 'root', host: str = '127.0.0.1', database: str = 'test'):
    cnx = connector.connect(user=user, password=password, host=host, database=database)
    return cnx


"""执行一般数据库操作语句"""


def execute(cnx: connector.MySQLConnection, sql: str, params: tuple):
    cursor = cnx.cursor(buffered=True)
    cursor.execute(sql, params)
    chgNum = cursor.rowcount
    cnx.commit()

    cursor.close()
    return chgNum


def query(cnx: connector.MySQLConnection, sql, params: tuple):
    cursor = cnx.cursor(buffered=True)
    cursor.execute(sql, params)

    retRows = []
    for line in cursor:
        retRows.append(line)

    cursor.close()
    return retRows


# 执行其他数据库结构操作语句
def execute_dml_sql(cnx: connector.MySQLConnection, sql: str):
    cursor = cnx.cursor()
    cursor.execute(sql)
    cnx.commit()
    cursor.close()


# 删表操作
def drop_table(cnx: connector.MySQLConnection, tabName: str):
    sql = " TRUNCATE table {} ".format(tabName)
    cursor = cnx.cursor()
    cursor.execute(sql)
    cnx.commit()
    cursor.close()


# 建表
def create_table(cnx: connector.MySQLConnection, sql: str):
    cursor = cnx.cursor()
    cursor.execute(sql)
    cnx.commit()
    cursor.close()


# 关闭连接
def close_conn(cnx: connector.MySQLConnection):
    cnx.close()


# 关闭所有连接
def close_all():
    for sc in staticConnectors.keys():
        cnx = staticConnectors.get(sc)
        cnx.close()
